from tkinter import Tk,Label,Entry,Button,Menu
from tkinter.messagebox import showinfo
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import YELLOW

root=Tk()    #创建一个实例对象
root.title("新华都对账专用")   #标题
root.minsize(100,100)   #窗口大小（宽×高）

#输入日期
label1 = Label(root,text='月',bg='yellow')
label1.place(x=20,y=10,width=30,height=30)

entry1 = Entry(root)
entry1.place(x=60,y=10,width=30,height=30)
    
label2 = Label(root,text='日',bg='yellow')
label2.place(x=100,y=10,width=30,height=30)

entry2 = Entry(root)
entry2.place(x=140,y=10,width=30,height=30)
def show():
    sh = Tk()    #创建一个实例对象
    sh.title("明细对比查询")   #标题
    sh.minsize(410,360)   #窗口大小（宽×高）
    
    yue = entry1.get()
    r = entry2.get()
    if eval(r) > 9:
        date_lj = yue + r
        if eval(r) == 10:
            date_cs = yue + '09'
        else:
            date_cs = yue + str(eval(r)-1)
    else:
        date_lj = yue + '0' + r
        if r == '1':
            if eval(yue) in [2,4,6,8,9,11]:
                date_cs = str(eval(yue)-1) + '31'
            elif eval(yue) == 3:
                date_cs = str(eval(yue)-1) + '28'
            elif eval(yue) == 1:
                date_cs = '1231'
            else:
                date_cs = str(eval(yue)-1) + '30'
        else:
            date_cs = yue + '0' + str(eval(r)-1)
        
    label0 = Label(sh,text='林记水产'+date_lj[:-2]+'月'+date_lj[-2:]+'日',bg='white',font=("楷体",12))
    label0.place(x=20,y=10,width=140,height=30)
    label1 = Label(sh,text='超市'+date_cs[:-2]+'月'+date_cs[-2:]+'日',bg='white',font=("楷体",12))
    label1.place(x=165,y=10,width=140,height=30)
    
    wb = load_workbook('新华都进货.xlsx')
    sheet1 = wb['新华都进货明细']
    sheet2 = wb['新华都超市手写单销售明细']
    li_jinhuo = []
    for i in sheet1['A3':'F10000']:
        if i[0].value == None or i[2].value == None:
            break
        if i[2].value == '丁贵':
            i[2].value = '丁桂'
        if i[2].value == '虾':
            i[2].value = '明虾'
        if i[1].value < 10:
            date1 = str(i[0].value) + '0' + str(i[1].value)
        else:
            date1 = str(i[0].value) + str(i[1].value)
        if date1 == date_lj:
            li_jinhuo.append(i[2].value + '_' + str(i[3].value) + '_' + str(i[4].value))
        
    li_xiaoshou = []
    for j in sheet2['A3':'F10000']:
        if j[0].value == None or j[2].value == None:
            break
        if j[2].value == '龙虾(波龙）' or j[2].value == '龙虾(波龙)' or j[2].value == '龙虾（波龙）' or j[2].value == '龙虾（波龙)':
            j[2].value = '波龙'
        if j[2].value == '小红斑':
            j[2].value = '红鱼'
        if j[1].value < 10:
            date2 = str(j[0].value) + '0' + str(j[1].value)
        else:
            date2 = str(j[0].value) + str(j[1].value)
        if date2 == date_cs:
            li_xiaoshou.append(j[2].value + '_' + str(j[3].value*2) + '_' + str(j[4].value))
        
    li_jinhuo = sorted(li_jinhuo)
    li_xiaoshou = sorted(li_xiaoshou)
    for n in li_xiaoshou:
        if '死虾' in n:
            q = n
            li_xiaoshou.remove(n)
            li_xiaoshou.append(q)
    #print(li_jinhuo,li_xiaoshou)
    def watch(x):
        data1,data2 = [],[]  #用于储存清洗后的数据，以输入Excel
        l = max(len(li_jinhuo),len(li_xiaoshou))
        heji1 = 0
        for k in range(len(li_jinhuo)):
            # 定位节点
            w = []  # 储存'_'所在位置
            index = -1
            while True:
                index = li_jinhuo[k].find('_', index + 1)  # 从index+1位置开始找，如果找到返回索引，没找到则返回-1
                w.append(index)
                if index == -1:  # 没找到，结束
                    break
            label = Label(sh,text=li_jinhuo[k][:w[0]],bg='white')
            label.place(x=20,y=40+20*k,width=70,height=20)
            if x == 1:
                label = Label(sh,text=li_jinhuo[k][w[0]+1:w[1]],bg='white')
                label.place(x=90,y=40+20*k,width=70,height=20)
                heji1 += eval(li_jinhuo[k][w[0]+1:w[1]])
            elif x == 2:
                label = Label(sh,text=li_jinhuo[k][w[1]+1:],bg='white')
                label.place(x=90,y=40+20*k,width=70,height=20)
            else:
                t = str(round(eval(li_jinhuo[k][w[0]+1:w[1]])*eval(li_jinhuo[k][w[1]+1:]),1))
                label = Label(sh,text=t,bg='white')
                label.place(x=90,y=40+20*k,width=70,height=20)
                heji1 += eval(li_jinhuo[k][w[0]+1:w[1]])*eval(li_jinhuo[k][w[1]+1:])
            data1.append([eval(yue),eval(r),li_jinhuo[k][:w[0]],eval(li_jinhuo[k][w[0]+1:w[1]]),eval(li_jinhuo[k][w[1]+1:]),
                          round(eval(li_jinhuo[k][w[0]+1:w[1]])*eval(li_jinhuo[k][w[1]+1:]),1)])
        label = Label(sh,text='合计',bg='white')
        label.place(x=20,y=42+20*l,width=70,height=20)
        label = Label(sh,text=str(round(heji1,1)),bg='white')
        label.place(x=90,y=42+20*l,width=70,height=20)

        heji2 = 0
        for m in range(len(li_xiaoshou)):
            # 定位节点
            w = []  # 储存'_'所在位置
            index = -1
            while True:
                index = li_xiaoshou[m].find('_', index + 1)  # 从index+1位置开始找，如果找到返回索引，没找到则返回-1
                w.append(index)
                if index == -1:  # 没找到，结束
                    break
            label = Label(sh,text=li_xiaoshou[m][:w[0]],bg='white')
            label.place(x=165,y=40+20*m,width=70,height=20)
            if x == 1:
                label = Label(sh,text=li_xiaoshou[m][w[0]+1:w[1]],bg='white')
                label.place(x=235,y=40+20*m,width=70,height=20)
                heji2 += eval(li_xiaoshou[m][w[0]+1:w[1]])
            elif x == 2:
                label = Label(sh,text=li_xiaoshou[m][w[1]+1:],bg='white')
                label.place(x=235,y=40+20*m,width=70,height=20)
            else:
                t = str(round(eval(li_xiaoshou[m][w[0]+1:w[1]])*eval(li_xiaoshou[m][w[1]+1:])/2,1))
                label = Label(sh,text=t,bg='white')
                label.place(x=235,y=40+20*m,width=70,height=20)
                heji2 += eval(li_xiaoshou[m][w[0]+1:w[1]])*eval(li_xiaoshou[m][w[1]+1:])/2
            data2.append([eval(yue),int(date_cs[-2:]),li_xiaoshou[m][:w[0]],eval(li_xiaoshou[m][w[0]+1:w[1]]),eval(li_xiaoshou[m][w[1]+1:]),
                          round(eval(li_xiaoshou[m][w[0]+1:w[1]])*eval(li_xiaoshou[m][w[1]+1:])/2,1)])
        label = Label(sh,text='合计',bg='white')
        label.place(x=165,y=42+20*l,width=70,height=20)
        label = Label(sh,text=str(round(heji2,1)),bg='white')
        label.place(x=235,y=42+20*l,width=70,height=20)

        def into():
            sheet3 = wb['新华都对比单']
            h1_sl,h2_sl,h1_money,h2_money = 0,0,0,0
            for num in range(len(data2)):
                try:
                    data = data2[num]+['']+data1[num]
                    h1_sl += data[3]
                    h2_sl += data[10]
                    h1_money += data[5]
                    h2_money += data[12]
                except IndexError:
                    data = data2[num]
                    h1_sl += data[3]
                    h1_money += data[5]
                print(data)
                sheet3.append(data)
                del data
            content_fill = PatternFill(fill_type='solid',fgColor=YELLOW)
            sheet3.append([eval(yue),int(date_cs[-2:]),'合计',h1_sl,'',h1_money,'','','','',h2_sl,'',h2_money])
            print(len(list(sheet3.rows)))
            for cell in list(sheet3.rows)[-1]:
                cell.fill = content_fill
            wb.save('新华都进货.xlsx')
            to = Tk()    #创建一个实例对象
            to.title("明细对比查询")   #标题
            to.minsize(60,60)   #窗口大小（宽×高）
            label = Label(to,text='导入成功',font=('楷体',15))
            label.place(x=60,y=50,width=80,height=40)
        button_into = Button(sh, text='数据导入到\n源Excel表格', command=into)
        button_into.place(x=310, y=200, width=80, height=40)
        
    button_into = Button(sh, text='对比数量', command=lambda:watch(1))
    button_into.place(x=310, y=20, width=95, height=30)

    button_into = Button(sh, text='对比单价', command=lambda:watch(2))
    button_into.place(x=310, y=80, width=95, height=30)

    button_into = Button(sh, text='对比合计金额', command=lambda:watch(3))
    button_into.place(x=310, y=140, width=95, height=30)

def about():
    abc='''
            本软件仅可用于新华都表格对账使用，基本可以完成
    自动化对账，不合理之处请及时联系处理。
    '''
    showinfo(title='帮助文件', message=abc)

    
menubar = Menu(root)
helpmenu = Menu(menubar, tearoff=0)
helpmenu.add_command(label='帮助文档', command=about)
menubar.add_cascade(label='帮助(H)', menu=helpmenu)
root.config(menu=menubar)

button_show = Button(root, text='查看', command=show)
button_show.place(x=50, y=100, width=90, height=60)

root.mainloop()
